package db_servicer

import (
	//    "fmt"
	"fmt"
	"log"
	"os"
	"time"

	"gopkg.in/rana/ora.v3"
	///   "bytes"
	//test purpose , can be removed later
	//     "reflect"
)

var ses *ora.Ses
var logger *log.Logger
var DB_Fail chan bool
var DB_Reconnect chan bool
var DB_FailCt int8

func Close() {
	defer ses.Close()

	//	logger = log.New(os.Stdout, "Close  logger:", log.Lshortfile|log.Lmicroseconds)

	logger.Println(" Closing DB  ... ") //, time.Now())

	//   fmt.Fprint(os.Stdout, os.Stdout )
}
func InitDB(c chan bool) {
	logger.Println("Initilizing DB .... .. . ") //, time.Now())
	var guser, gorap, gdb string

	guser = os.Getenv("DUSER")
	gorap = os.Getenv("ORAP")
	gdb = os.Getenv("DBLNK")

	env, err := ora.OpenEnv(nil)

	if err != nil {
		fmt.Printf(fmt.Sprintf("Error in ora.OpenEnv(nil):%v\n", err))
		DB_Reconnect <- true
		return
	}
	defer env.Close()

	srvCfg := ora.SrvCfg{Dblink: gdb} // "ifmstonda"}
	srv, err := env.OpenSrv(&srvCfg)
	if err != nil {
		fmt.Printf(fmt.Sprintf("Error in ora.OpenSrv(&srvCfgnil):%v\n", err))
		DB_Reconnect <- true
		return
	}
	defer srv.Close()
	sesCfg := ora.SesCfg{
		Username: guser,
		Password: gorap,
	}
	ses, err = srv.OpenSes(&sesCfg)
	if err != nil {
		fmt.Printf(fmt.Sprintf("Error in ora.OpenSes(&sesCfg):%v\n", err))
		DB_Reconnect <- true
		return
	}
	defer ses.Close()
	//    fmt.Println("Now Connected to Oracle ", time.Now())
	logger.Println("Now Connected to Oracle ") //, time.Now())

	go func() {

		var ct int64

		// stmt, err := ses.Prep("SELECT Count(1) into :c1 FROM open_cursor where user_name =upper(:u)")
		stmt, err := ses.Prep("call get_open_cursor( :ct)")

		if err != nil {
			logPanic(fmt.Sprintf("Error in ses.Prep( max open cursor):%v\n", err))

		}
		defer stmt.Close()

		for {

			stmt.Exe(&ct)
			logger.Printf(">>>>checking open cursors for user:%v, ct: %v \n ", guser, ct)
			if ct > 260 {
				logger.Printf(">>>>>>>>>Open Cursor exceeds: %v, will re-connect session.\n ", ct)
				break
			}

			time.Sleep(60 * time.Second)
		}
		c <- true

	}()
	<-c
	logger.Println(">>>>>>>>> Oracle Connection Closed") //, time.Now())
	DB_Reconnect <- true
	// close(DB_Reconnect)
}

func init() {
	logger = log.New(os.Stdout, "DB SVR>", log.Lshortfile|log.LstdFlags) //log.LstdFlags
	//    fmt.Fprintf(os.Stdout,"Initilizing DB .... .. . : %s " , time.Now())
	DB_Fail = make(chan bool)
	DB_Reconnect = make(chan bool)
	DB_FailCt = 0

	go func() {

		for {
			if ses == nil || !ses.IsOpen() {
				// msg := fmt.Sprintf(">>>>>>>>>>>>>>>>>> Init loop : ses:%v", ses)
				// if ses != nil {
				// 	msg = fmt.Sprintf(" %v, ses.IsOpen():%v", msg, ses.IsOpen())
				// }
				// logger.Printf("%v\n", msg)
				// DB_Fail = make(chan bool)
				// DB_Reconnect = make(chan bool)
				go InitDB(DB_Fail)

				// logger.Printf("Before <- DB_Reconnect.......")

				<-DB_Reconnect
				time.Sleep(7 * time.Second)
				// logger.Printf("After <- DB_Reconnect.......")
			}
		}
	}()

}

func logPanic(msg string) {
	defer func() {
		if DB_FailCt < 10 {

			if r := recover(); r != nil {

				// logger.Println(r)
				// logger.Printf("In Recover, %v times...., before DB_Fail <- true \n", DB_FailCt)
				DB_Fail <- true
				time.Sleep(3 * time.Second)
				// if DB_Fail != nil {
				// 	close(DB_Fail)
				//				}
				// logger.Printf("In Recover, %v times...., after DB_Fail <- true \n", DB_FailCt)
			}
		} else {
			logger.Printf("logPanic, too many failure:%v, no more recover. \n", DB_FailCt)
		}
		DB_FailCt++

	}()
	logger.Panic(msg)
}

/*
var buf bytes.Buffer

var rowct int

func Insert( str string ) {
    // example usage of the ora package driver
    // connect to a server and open a session
    tableName := "t1"
    logger = log.New(os.Stdout , " Insert logger:", log.Lshortfile| log.Lmicroseconds)

    // begin first transaction
    tx1, err := ses.StartTx()
    if err != nil {
        panic(err)
    }

    // insert record
    var id uint64
    stmtIns, err := ses.Prep(fmt.Sprintf(
        "INSERT INTO %v (C2) VALUES (:C2) RETURNING C1 INTO :C1", tableName))
    defer stmtIns.Close()
//    rowsAffected, err := stmtIns.Exe(str, &id)
    _, err = stmtIns.Exe(str, &id)
    if err != nil {
        panic(err)
    }
//    fmt.Println(rowsAffected)
    logger.Println("Inserted done at : ", time.Now())

    // fetch records
    stmtQry, err := ses.Prep(fmt.Sprintf(
        "SELECT C1, C2 FROM %v", tableName))
    defer stmtQry.Close()
    if err != nil {
        panic(err)
    }
    rset, err := stmtQry.Qry()
    if err != nil {
        panic(err)
    }
    rowct = 0
    for rset.Next() {
//        fmt.Println(rset.Row[0], rset.Row[1])
        rowct ++;
    }
    if rset.Err != nil {
        panic(rset.Err)
    }

    // commit first transaction
    err = tx1.Commit()
    if err != nil {
        panic(err)
    }
//    fmt.Println("Inserted done at : ", time.Now())
    logger.Println("Fetch rows: ", rset.Len(), ", Rowct: ", rowct, " at: " ,time.Now() )

}

func CallProc() {

//   // create stored procedure with sys_refcursor
//   stmtProcCreate, err := ses.Prep(fmt.Sprintf(
//       "CREATE OR REPLACE PROCEDURE PROC1(P1 OUT SYS_REFCURSOR) AS BEGIN "+
//           "OPEN P1 FOR SELECT C1, C2 FROM %v WHERE C1 > 2 ORDER BY C1; "+
//           "END PROC1;",
//       tableName))
//   defer stmtProcCreate.Close()
//   rowsAffected, err = stmtProcCreate.Exe()
//   if err != nil {
//       panic(err)
//   }

    // call stored procedure
    // pass *Rset to Exe to receive the results of a sys_refcursor
    logger = log.New(os.Stdout , "Proc  logger:", log.Lshortfile| log.Lmicroseconds)


    stmtProcCall, err := ses.Prep("CALL PROC1(:1)")
    defer stmtProcCall.Close()
    if err != nil {
        panic(err)
    }
    procRset := &ora.Rset{}
//    rowsAffected, err := stmtProcCall.Exe(procRset)
    _, err = stmtProcCall.Exe(procRset)
    if err != nil {
        panic(err)
    }
    if procRset.IsOpen() {
        for i:=0 ;i < rowct - 2 && procRset.Next() ;  i++ {
//            procRset.Next()
        }
        for procRset.Next() {
            fmt.Println(procRset.Row[0], procRset.Row[1], procRset.Row[2])
        }
        if procRset.Err != nil {
            panic(procRset.Err)
        }
//        fmt.Println("Total Rows:", procRset.Len(), ",  rsetCt: " , rsetCt )
                logger.Println(" Porc Total Rows:", procRset.Len() )
    }

//    fmt.Println("Rows Affected: ", rowsAffected)
//    fmt.Println("Proc end at: ", time.Now())
    logger.Println("Proc end at: ", time.Now())

}
*/
